<?

/**
 * dbStatements.php
 *
 * @package adServer
 */

define('SQL_CREATE_DATABASE', 'CREATE DATABASE %%DBNAME%%');

define('SQL_CREATE_COLLECTION_TABLE', "CREATE TABLE  `ad_collection` (
  `adUnitId` int(10) unsigned NOT NULL auto_increment,
  `adTypeId` int(10) unsigned NOT NULL default '0',
  `adDuration` int(10) unsigned NOT NULL,
  `adStartOffsetPercent` int(10) unsigned NOT NULL default '0',
  `adHtml` text ,
  `adCampaign` varchar(100) NOT NULL default '*',
  `adLanguageISO639` varchar(2) NOT NULL default '*',
  `adDisplayedCount` int(10) unsigned NOT NULL default '0',  
  PRIMARY KEY  (`adUnitId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;");

define('SQL_CREATE_ADUNIT', "INSERT INTO `ad_collection`
  VALUES ('','%%TYPEID%%','%%DURATION%%','%%OFFSET%%','%%HTML%%','%%CAMPAIGN%%','%%LANGUAGE%%','')");

define('SQL_GET_ADUNITS', "SELECT c.adUnitId as adUnitId,t.typeName as adType,
c.adDuration as adDuration,c.adStartOffsetPercent as `adStart (%)`,c.adCampaign as adCampaign,
c.adLanguageISO639 as adLanguage FROM ad_collection as c JOIN ad_type as t ON c.adTypeId = t.typeId");

define('SQL_GET_AD_DETAILS',
    "SELECT * FROM ad_collection WHERE adUnitId = %%ADUNITID%%");

define('SQL_GET_AD_BY_CRITERIA',
    "SELECT * FROM ad_collection WHERE adTypeId = %%TYPEID%% &&
 (adLanguageISO639 LIKE '%%LANGUAGE%%' || adLanguageISO639 = '--') &&
 adCampaign LIKE '%%CAMPAIGN%%'  ORDER BY adDisplayedCount ASC ");
 
define('SQL_GET_AD_BY_CRITERIA_NOTYPE',
    "SELECT * FROM ad_collection WHERE 
 (adLanguageISO639 LIKE '%%LANGUAGE%%' || adLanguageISO639 = '--') &&
 adCampaign LIKE '%%CAMPAIGN%%'  ORDER BY adDisplayedCount ASC ");

define('SQL_INCREMENT_AD_COUNTER', "UPDATE ad_collection 
SET `adDisplayedCount`=%%NEWCOUNT%% WHERE adUnitId = %%ADUNITID%%");


define('SQL_DELETE_AD',
    "DELETE FROM ad_collection WHERE adUnitId = %%ADUNITID%%");

define('SQL_UPDATE_AD', "UPDATE ad_collection 
SET `adTypeId`=%%TYPEID%%,`adDuration` = '%%DURATION%%',`adStartOffsetPercent`='%%OFFSET%%',
`adHtml` ='%%HTML%%' , `adCampaign`='%%CAMPAIGN%%',`adLanguageISO639`='%%LANGUAGE%%'WHERE adUnitId = %%ADUNITID%%");

define('SQL_CREATE_ADKEYWORD_TABLE', "CREATE TABLE `ad_keyword` (
  `adKeywordId` int(10) unsigned NOT NULL auto_increment,
  `adUnitId` int(10) unsigned NOT NULL default '0',
  `keywordId` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`adKeywordId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;");

define('SQL_CREATE_LOG_TABLE', "CREATE TABLE `ad_log` (
  `adUnitId` int(10) unsigned NOT NULL,
  `country` varchar(3) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `logId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`logId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;");


define('SQL_CREATE_KEYWORD_TABLE', "CREATE TABLE `keywords` (
  `keywordId` int(10) unsigned NOT NULL auto_increment,
  `keyword` varchar(45) NOT NULL,
  PRIMARY KEY  (`keywordId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;;");

define('SQL_CREATE_USERS_TABLE', "CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;");

define('SQL_CREATE_CAMPAIGNS_TABLE', "CREATE TABLE `ad_campaigns` (
  `campaignId` int(10) unsigned NOT NULL auto_increment,
  `campaign` varchar(45) NOT NULL,
  `description` text ,
  PRIMARY KEY  (`campaignId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;");


define('SQL_CREATE_USER', "INSERT INTO `users`
  SET `username` = '%%USER%%',`password`='%%PASSWORD%%'");

define('SQL_CREATE_CAMPAIGNS_ENTRY',
    "INSERT INTO `ad_campaigns` SET `campaign` = '%%CAMPAIGN%%',`description` = '%%DESCRIPTION%%' ;");

define('SQL_DELETE_CAMPAIGNS_ENTRY',
    "DELETE FROM `ad_campaigns` WHERE `campaign` = '%%CAMPAIGN%%';");


define('SQL_GET_CAMPAIGNS',
    "SELECT `campaign`,`description` FROM `ad_campaigns`;");

define('SQL_CAMPAIGN_EXISTS',
    "SELECT IF((SELECT count(*) from ad_campaigns where campaign ='%%CAMPAIGN%%')>= 1,true,false);");


define('SQL_USER_EXISTS',
    "SELECT IF((SELECT count(*) from users where username ='%%USER%%')>= 1,true,false);");

define('SQL_USER_LOGIN',
    "SELECT IF((SELECT password from users where username ='%%USER%%')= '%%PASSWORD%%',true,false);");


define('SQL_CREATE_TYPE_TABLE', "CREATE TABLE `ad_type` (
  `typeId` int(10) unsigned NOT NULL auto_increment,
  `typeName` varchar(45) NOT NULL,
  `typeDescription` varchar(150) NOT NULL default 'Description not available',
  PRIMARY KEY  (`typeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `ad_type` VALUES ('1', 'Ad-Plate', 'Static HTML code, image');
INSERT INTO `ad_type` VALUES ('2', 'DivX-Roll', 'DivX embed advertisement');
INSERT INTO `ad_type` VALUES ('3', 'Flash-Roll', 'Flash advertisement');
");

define('SQL_GET_TYPES', "SELECT typeId,typeName,typeDescription FROM `ad_type`;");

define('SQL_GET_TYPENAME_BY_ID',
    "SELECT typeName FROM `ad_type` WHERE typeId ='%%TYPEID%%' ;");


define('SQL_CREATE_LANGUAGE_TABLE', "CREATE TABLE `languages` (
  `langID` smallint(3) NOT NULL AUTO_INCREMENT,
  `language` char(49) DEFAULT NULL,
  `code` char(2) DEFAULT NULL,
  PRIMARY KEY (`langID`)
) ENGINE=MyISAM AUTO_INCREMENT=137 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `languages` VALUES ('0', 'All', '--');
INSERT INTO `languages` VALUES ('1', 'English', 'en');
INSERT INTO `languages` VALUES ('2', 'Afar', 'aa');
INSERT INTO `languages` VALUES ('3', 'Abkhazian', 'ab');
INSERT INTO `languages` VALUES ('4', 'Afrikaans', 'af');
INSERT INTO `languages` VALUES ('5', 'Amharic', 'am');
INSERT INTO `languages` VALUES ('6', 'Arabic', 'ar');
INSERT INTO `languages` VALUES ('7', 'Assamese', 'as');
INSERT INTO `languages` VALUES ('8', 'Aymara', 'ay');
INSERT INTO `languages` VALUES ('9', 'Azerbaijani', 'az');
INSERT INTO `languages` VALUES ('10', 'Bashkir', 'ba');
INSERT INTO `languages` VALUES ('11', 'Byelorussian', 'be');
INSERT INTO `languages` VALUES ('12', 'Bulgarian', 'bg');
INSERT INTO `languages` VALUES ('13', 'Bihari', 'bh');
INSERT INTO `languages` VALUES ('14', 'Bislama', 'bi');
INSERT INTO `languages` VALUES ('15', 'Bengali/Bangla', 'bn');
INSERT INTO `languages` VALUES ('16', 'Tibetan', 'bo');
INSERT INTO `languages` VALUES ('17', 'Breton', 'br');
INSERT INTO `languages` VALUES ('18', 'Catalan', 'ca');
INSERT INTO `languages` VALUES ('19', 'Corsican', 'co');
INSERT INTO `languages` VALUES ('20', 'Czech', 'cs');
INSERT INTO `languages` VALUES ('21', 'Welsh', 'cy');
INSERT INTO `languages` VALUES ('22', 'Danish', 'da');
INSERT INTO `languages` VALUES ('23', 'German', 'de');
INSERT INTO `languages` VALUES ('24', 'Bhutani', 'dz');
INSERT INTO `languages` VALUES ('25', 'Greek', 'el');
INSERT INTO `languages` VALUES ('26', 'Esperanto', 'eo');
INSERT INTO `languages` VALUES ('27', 'Spanish', 'es');
INSERT INTO `languages` VALUES ('28', 'Estonian', 'et');
INSERT INTO `languages` VALUES ('29', 'Basque', 'eu');
INSERT INTO `languages` VALUES ('30', 'Persian', 'fa');
INSERT INTO `languages` VALUES ('31', 'Finnish', 'fi');
INSERT INTO `languages` VALUES ('32', 'Fiji', 'fj');
INSERT INTO `languages` VALUES ('33', 'Faeroese', 'fo');
INSERT INTO `languages` VALUES ('34', 'French', 'fr');
INSERT INTO `languages` VALUES ('35', 'Frisian', 'fy');
INSERT INTO `languages` VALUES ('36', 'Irish', 'ga');
INSERT INTO `languages` VALUES ('37', 'Scots/Gaelic', 'gd');
INSERT INTO `languages` VALUES ('38', 'Galician', 'gl');
INSERT INTO `languages` VALUES ('39', 'Guarani', 'gn');
INSERT INTO `languages` VALUES ('40', 'Gujarati', 'gu');
INSERT INTO `languages` VALUES ('41', 'Hausa', 'ha');
INSERT INTO `languages` VALUES ('42', 'Hindi', 'hi');
INSERT INTO `languages` VALUES ('43', 'Croatian', 'hr');
INSERT INTO `languages` VALUES ('44', 'Hungarian', 'hu');
INSERT INTO `languages` VALUES ('45', 'Armenian', 'hy');
INSERT INTO `languages` VALUES ('46', 'Interlingua', 'ia');
INSERT INTO `languages` VALUES ('47', 'Interlingue', 'ie');
INSERT INTO `languages` VALUES ('48', 'Inupiak', 'ik');
INSERT INTO `languages` VALUES ('49', 'Indonesian', 'in');
INSERT INTO `languages` VALUES ('50', 'Icelandic', 'is');
INSERT INTO `languages` VALUES ('51', 'Italian', 'it');
INSERT INTO `languages` VALUES ('52', 'Hebrew', 'iw');
INSERT INTO `languages` VALUES ('53', 'Japanese', 'ja');
INSERT INTO `languages` VALUES ('54', 'Yiddish', 'ji');
INSERT INTO `languages` VALUES ('55', 'Javanese', 'jw');
INSERT INTO `languages` VALUES ('56', 'Georgian', 'ka');
INSERT INTO `languages` VALUES ('57', 'Kazakh', 'kk');
INSERT INTO `languages` VALUES ('58', 'Greenlandic', 'kl');
INSERT INTO `languages` VALUES ('59', 'Cambodian', 'km');
INSERT INTO `languages` VALUES ('60', 'Kannada', 'kn');
INSERT INTO `languages` VALUES ('61', 'Korean', 'ko');
INSERT INTO `languages` VALUES ('62', 'Kashmiri', 'ks');
INSERT INTO `languages` VALUES ('63', 'Kurdish', 'ku');
INSERT INTO `languages` VALUES ('64', 'Kirghiz', 'ky');
INSERT INTO `languages` VALUES ('65', 'Latin', 'la');
INSERT INTO `languages` VALUES ('66', 'Lingala', 'ln');
INSERT INTO `languages` VALUES ('67', 'Laothian', 'lo');
INSERT INTO `languages` VALUES ('68', 'Lithuanian', 'lt');
INSERT INTO `languages` VALUES ('69', 'Latvian/Lettish', 'lv');
INSERT INTO `languages` VALUES ('70', 'Malagasy', 'mg');
INSERT INTO `languages` VALUES ('71', 'Maori', 'mi');
INSERT INTO `languages` VALUES ('72', 'Macedonian', 'mk');
INSERT INTO `languages` VALUES ('73', 'Malayalam', 'ml');
INSERT INTO `languages` VALUES ('74', 'Mongolian', 'mn');
INSERT INTO `languages` VALUES ('75', 'Moldavian', 'mo');
INSERT INTO `languages` VALUES ('76', 'Marathi', 'mr');
INSERT INTO `languages` VALUES ('77', 'Malay', 'ms');
INSERT INTO `languages` VALUES ('78', 'Maltese', 'mt');
INSERT INTO `languages` VALUES ('79', 'Burmese', 'my');
INSERT INTO `languages` VALUES ('80', 'Nauru', 'na');
INSERT INTO `languages` VALUES ('81', 'Nepali', 'ne');
INSERT INTO `languages` VALUES ('82', 'Dutch', 'nl');
INSERT INTO `languages` VALUES ('83', 'Norwegian', 'no');
INSERT INTO `languages` VALUES ('84', 'Occitan', 'oc');
INSERT INTO `languages` VALUES ('85', '(Afan)/Oromoor/Oriya', 'om');
INSERT INTO `languages` VALUES ('86', 'Punjabi', 'pa');
INSERT INTO `languages` VALUES ('87', 'Polish', 'pl');
INSERT INTO `languages` VALUES ('88', 'Pashto/Pushto', 'ps');
INSERT INTO `languages` VALUES ('89', 'Portuguese', 'pt');
INSERT INTO `languages` VALUES ('90', 'Quechua', 'qu');
INSERT INTO `languages` VALUES ('91', 'Rhaeto-Romance', 'rm');
INSERT INTO `languages` VALUES ('92', 'Kirundi', 'rn');
INSERT INTO `languages` VALUES ('93', 'Romanian', 'ro');
INSERT INTO `languages` VALUES ('94', 'Russian', 'ru');
INSERT INTO `languages` VALUES ('95', 'Kinyarwanda', 'rw');
INSERT INTO `languages` VALUES ('96', 'Sanskrit', 'sa');
INSERT INTO `languages` VALUES ('97', 'Sindhi', 'sd');
INSERT INTO `languages` VALUES ('98', 'Sangro', 'sg');
INSERT INTO `languages` VALUES ('99', 'Serbo-Croatian', 'sh');
INSERT INTO `languages` VALUES ('100', 'Singhalese', 'si');
INSERT INTO `languages` VALUES ('101', 'Slovak', 'sk');
INSERT INTO `languages` VALUES ('102', 'Slovenian', 'sl');
INSERT INTO `languages` VALUES ('103', 'Samoan', 'sm');
INSERT INTO `languages` VALUES ('104', 'Shona', 'sn');
INSERT INTO `languages` VALUES ('105', 'Somali', 'so');
INSERT INTO `languages` VALUES ('106', 'Albanian', 'sq');
INSERT INTO `languages` VALUES ('107', 'Serbian', 'sr');
INSERT INTO `languages` VALUES ('108', 'Siswati', 'ss');
INSERT INTO `languages` VALUES ('109', 'Sesotho', 'st');
INSERT INTO `languages` VALUES ('110', 'Sundanese', 'su');
INSERT INTO `languages` VALUES ('111', 'Swedish', 'sv');
INSERT INTO `languages` VALUES ('112', 'Swahili', 'sw');
INSERT INTO `languages` VALUES ('113', 'Tamil', 'ta');
INSERT INTO `languages` VALUES ('114', 'Tegulu', 'te');
INSERT INTO `languages` VALUES ('115', 'Tajik', 'tg');
INSERT INTO `languages` VALUES ('116', 'Thai', 'th');
INSERT INTO `languages` VALUES ('117', 'Tigrinya', 'ti');
INSERT INTO `languages` VALUES ('118', 'Turkmen', 'tk');
INSERT INTO `languages` VALUES ('119', 'Tagalog', 'tl');
INSERT INTO `languages` VALUES ('120', 'Setswana', 'tn');
INSERT INTO `languages` VALUES ('121', 'Tonga', 'to');
INSERT INTO `languages` VALUES ('122', 'Turkish', 'tr');
INSERT INTO `languages` VALUES ('123', 'Tsonga', 'ts');
INSERT INTO `languages` VALUES ('124', 'Tatar', 'tt');
INSERT INTO `languages` VALUES ('125', 'Twi', 'tw');
INSERT INTO `languages` VALUES ('126', 'Ukrainian', 'uk');
INSERT INTO `languages` VALUES ('127', 'Urdu', 'ur');
INSERT INTO `languages` VALUES ('128', 'Uzbek', 'uz');
INSERT INTO `languages` VALUES ('129', 'Vietnamese', 'vi');
INSERT INTO `languages` VALUES ('130', 'Volapuk', 'vo');
INSERT INTO `languages` VALUES ('131', 'Wolof', 'wo');
INSERT INTO `languages` VALUES ('132', 'Xhosa', 'xh');
INSERT INTO `languages` VALUES ('133', 'Yoruba', 'yo');
INSERT INTO `languages` VALUES ('134', 'Chinese', 'zh');
INSERT INTO `languages` VALUES ('135', 'Zulu', 'zu');");

define('SQL_GET_LANGUAGES', "SELECT `language`,`code` FROM `languages` ORDER BY language;");

define('SQL_SHOW_TABLE',"SHOW tables LIKE '%%TABLENAME%%'");
?>
